/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccessor;

import Config.ConfigSetting;
import CrlException.CrlException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.LinkedList;

/**
 *
 * @author CUHUUHOANG
 */
public class DatabaseAccessor {

    private static String[] allAtributes; //String[0] la key
    private static String tableName;
    private static String DatabaseName;
    private static String hostMySQL;
    private static String username;
    private static String password;
    private boolean isUsingDatabase;

    public DatabaseAccessor(String[] allAtributes, String tableName, String hostMySQL, String DatabaseName, String userName, String Password) {
        this.allAtributes = allAtributes;
        this.tableName = tableName;
        this.DatabaseName = DatabaseName;
        this.hostMySQL = hostMySQL;
        this.username = userName;
        this.password = Password;
    }

    public DatabaseAccessor() {

        this.allAtributes = ConfigSetting.allAttribute;
        this.tableName = ConfigSetting.tableName;
        this.DatabaseName = ConfigSetting.databaseName;
        hostMySQL = ConfigSetting.hostMySQL;
        username = ConfigSetting.username;
        password = ConfigSetting.password;
        isUsingDatabase = false;
    }

    synchronized public boolean TableIsExisted() throws CrlException {
        boolean result = false;
        Connection conn = null;
        try {
            checkUsingDatabase();
            isUsingDatabase = true;
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(hostMySQL + DatabaseName, username, password);
            DatabaseMetaData dbm = conn.getMetaData();

            ResultSet tables = dbm.getTables(null, null, tableName, null);
            if (tables.next()) {
                // Table exists
                result = true;
            } else {
                // Table does not exist
                result = false;
            }
            isUsingDatabase = false;
            notifyAll();
        } catch (Exception ex) {
            throw new CrlException(ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ex) {
                    throw new CrlException(ex.getMessage());
                }
            }
            return result;
        }
    }

    synchronized public void CreateTable() throws CrlException {
        Connection conn = null;
        Statement stmt = null;
        try {
            checkUsingDatabase();
            isUsingDatabase = true;

            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(hostMySQL + DatabaseName, username, password);
            stmt = conn.createStatement();

            // Query
            String sql = "CREATE TABLE " + tableName + " (";
            for (int i = 0; i < allAtributes.length; i++) {
                if (i == 0) {
                    sql += allAtributes[0] + " VARCHAR(255) not NULL";
                } else {
                    sql += "," + allAtributes[i] + " Text Null";
                }
            }
            sql += ", PRIMARY KEY (" + allAtributes[0] + "));";

            stmt.executeUpdate(sql);
            isUsingDatabase = false;
            notifyAll();
        } catch (Exception ex) {
            throw new CrlException(ex.getMessage());
//            System.out.println(ex);
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception ex) {
                throw new CrlException(ex.getMessage());
            }

        }

    }

    synchronized public void DropTable() throws CrlException {
        Connection conn = null;
        Statement stmt = null;
        try {
            checkUsingDatabase();
            isUsingDatabase = true;
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(hostMySQL + DatabaseName, username, password);
            stmt = conn.createStatement();

            // Query
            String sql = "DROP Table " + tableName;

            stmt.executeUpdate(sql);
            isUsingDatabase = false;
            notifyAll();
        } catch (Exception ex) {
            throw new CrlException(ex.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception ex) {
                throw new CrlException(ex.getMessage());
            }

        }
    }

    synchronized public void AddRow(Hashtable<String, String> row) throws CrlException {
        //trong nay se co du cac truong nhu da ghi o String[] allAtributes
        if (!row.containsKey(allAtributes[0])) {
            return;
        }
        Connection conn = null;
        try {
            checkUsingDatabase();
            isUsingDatabase = true;
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(hostMySQL + DatabaseName, username, password);

            // Query
            String sql = "INSERT INTO " + tableName + "(";
            for (int i = 0; i < allAtributes.length; i++) {
                if (i == 0) {
                    sql += allAtributes[i];
                } else {
                    sql += ", " + allAtributes[i];
                }
            }
            sql += ") Values (";
            for (int i = 0; i < allAtributes.length; i++) {
                if (i == 0) {
                    sql += "?";
                } else {
                    sql += ", ?";
                }
            }
            sql += ");";

            PreparedStatement prest = conn.prepareStatement(sql);
            for( int i = 0; i < allAtributes.length; i++){
                 prest.setString(i + 1, row.get(allAtributes[i]));
            }

            prest.executeUpdate();
            isUsingDatabase = false;
            notifyAll();
        } catch (Exception ex) {
            throw new CrlException(ex.getMessage());
        } finally {
            try {
                conn.close();
            } catch (Exception ex) {
                throw new CrlException(ex.getMessage());
            }

        }
    }

    synchronized public LinkedList getListAtribute(String AtributeName) throws CrlException {//lay list cac du lieu cua 1 attribute vi du nhu "url"
        LinkedList result = new LinkedList();
        Connection conn = null;
        Statement stmt = null;
        try {
            checkUsingDatabase();
            isUsingDatabase = true;
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(hostMySQL + DatabaseName, username, password);
            stmt = conn.createStatement();

            // Query
            String sql = "Select " + AtributeName + " FROM " + tableName;
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                result.add(rs.getString(AtributeName));
            }
            isUsingDatabase = false;
            notifyAll();
        } catch (Exception ex) {
            throw new CrlException(ex.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception ex) {
                throw new CrlException(ex.getMessage());
            }
        }
        return result;
    }

    synchronized private void checkUsingDatabase() throws CrlException {

        try {
            if (isUsingDatabase) {
                wait();
            }
        } catch (Exception e) {
            throw new CrlException(e.getMessage());
        }

    }

    synchronized public boolean valueExisted(String AtributeName, String value) throws CrlException {
        boolean out = false;
        Connection conn = null;
        Statement stmt = null;
        try {
            checkUsingDatabase();
            isUsingDatabase = true;
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(hostMySQL + DatabaseName, username, password);
            stmt = conn.createStatement();

            // Query
            String sql = "Select " + AtributeName + " FROM " + tableName + " WHERE " + AtributeName + " = '" + value + "' limit 1";


            ResultSet rs = stmt.executeQuery(sql);
            if (!rs.isBeforeFirst()) {
                out = true;
            }
            isUsingDatabase = false;
            notifyAll();
        } catch (Exception ex) {
            throw new CrlException(ex.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception ex) {
                throw new CrlException(ex.getMessage());
            }
        }
        return out;
    }
}
